/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package da;

import entities.Branch;
import entities.Customer;
import entities.LoanContact;
import entities.LoanType;
import entities.User;
import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Bich Tran Van
 */
public class DALoanContact {
    /*
     * To change this template, choose Tools | Templates
     * and open the template in the editor.
     */

    private DACustomer daCustomer = new DACustomer();
    private DALoanType daLoanType = new DALoanType();
    private ArrayList<Customer> listCustomer;
    private ArrayList<LoanType> listLoanType;
    private DAUser daUser = new DAUser();
    private ArrayList<User> listUser;
    MSSQLConnection conn = null;

    public DALoanContact() {
        try {
            conn = new MSSQLConnection();
            conn.getConnection();
        } catch (Exception ex) {
            Logger.getLogger(DALoanType.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    // get all loan type
    public ArrayList<LoanContact> getAll(User user) throws Exception {
        ArrayList<LoanContact> list = new ArrayList<LoanContact>();
        try {
            conn.getConnection();
            if (user.getUserType().getTypeId() == 1) {
                conn.cstm = conn.connect.prepareCall("{CALL View_all_LoanContact}");
            } else {
                conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_By_Branch(?)}");
                conn.cstm.setInt(1, user.getUserId());
            }
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                LoanContact loanContact = new LoanContact();

                loanContact.setLoanId(conn.rs.getInt("LoanID"));

                listCustomer = daCustomer.getByID(conn.rs.getInt("CustomerID"));
                Customer customer = listCustomer.get(0);
                loanContact.setCustomer(customer);

                listLoanType = daLoanType.getByID(conn.rs.getInt("LoanTypeID"));
                loanContact.setLoantype(listLoanType.get(0));


                listUser = daUser.getById(conn.rs.getInt("UserID"));
                loanContact.setUser(listUser.get(0));

                if (conn.rs.getInt("UserAccecpted") == 0) {
                    loanContact.setUserAccepted(null);
                } else {
                    listUser = daUser.getById(conn.rs.getInt("UserAccecpted"));
                    loanContact.setUserAccepted(listUser.get(0));
                }

                loanContact.setAmount(conn.rs.getDouble("Amount"));
                loanContact.setDateStart(conn.rs.getDate("DateStart"));
                loanContact.setPeriod(conn.rs.getInt("Period"));
                loanContact.setIsAccepted(conn.rs.getInt("IsAccepted"));
                list.add(loanContact);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    //get loan type by id
    public ArrayList<LoanContact> getByLoanID(int loanID, User user) throws Exception {
        ArrayList<LoanContact> list = new ArrayList<LoanContact>();
        try {
            conn.getConnection();
            if (user.getUserType().getTypeId() == 1) {
                conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_By_ID(?)}");
                conn.cstm.setInt(1, loanID);
            } else {
                conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_By_ID_branch(?,?)}");
                conn.cstm.setInt(1, user.getUserId());
                conn.cstm.setInt(2, loanID);
            }

            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                LoanContact loanContact = new LoanContact();

                loanContact.setLoanId(conn.rs.getInt("LoanID"));

                listCustomer = daCustomer.getByID(conn.rs.getInt("CustomerID"));
                Customer customer = listCustomer.get(0);
                loanContact.setCustomer(customer);

                listLoanType = daLoanType.getByID(conn.rs.getInt("LoanTypeID"));
                loanContact.setLoantype(listLoanType.get(0));


                listUser = daUser.getById(conn.rs.getInt("UserID"));
                loanContact.setUser(listUser.get(0));


                if (conn.rs.getInt("UserAccecpted") == 0) {
                    loanContact.setUserAccepted(null);
                } else {
                    listUser = daUser.getById(conn.rs.getInt("UserAccecpted"));
                    loanContact.setUserAccepted(listUser.get(0));
                }

                loanContact.setAmount(conn.rs.getDouble("Amount"));
                loanContact.setDateStart(conn.rs.getDate("DateStart"));
                loanContact.setPeriod(conn.rs.getInt("Period"));
                loanContact.setIsAccepted(conn.rs.getInt("IsAccepted"));
                list.add(loanContact);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public ArrayList<LoanContact> getByID(int loanID) throws Exception {
        ArrayList<LoanContact> list = new ArrayList<LoanContact>();
        try {
            conn.getConnection();

            conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_By_ID(?)}");
            conn.cstm.setInt(1, loanID);

            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                LoanContact loanContact = new LoanContact();

                loanContact.setLoanId(conn.rs.getInt("LoanID"));

                listCustomer = daCustomer.getByID(conn.rs.getInt("CustomerID"));
                Customer customer = listCustomer.get(0);
                loanContact.setCustomer(customer);

                listLoanType = daLoanType.getByID(conn.rs.getInt("LoanTypeID"));
                loanContact.setLoantype(listLoanType.get(0));


                listUser = daUser.getById(conn.rs.getInt("UserID"));
                loanContact.setUser(listUser.get(0));


                if (conn.rs.getInt("UserAccecpted") == 0) {
                    loanContact.setUserAccepted(null);
                } else {
                    listUser = daUser.getById(conn.rs.getInt("UserAccecpted"));
                    loanContact.setUserAccepted(listUser.get(0));
                }

                loanContact.setAmount(conn.rs.getDouble("Amount"));
                loanContact.setDateStart(conn.rs.getDate("DateStart"));
                loanContact.setPeriod(conn.rs.getInt("Period"));
                loanContact.setIsAccepted(conn.rs.getInt("IsAccepted"));
                list.add(loanContact);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public ArrayList<LoanContact> getByCustomerID(int customerID, User user) throws Exception {
        ArrayList<LoanContact> list = new ArrayList<LoanContact>();
        try {
            conn.getConnection();
            if (user.getUserType().getTypeId() == 1) {
                conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_By_CustomerID(?)}");
                conn.cstm.setInt(1, customerID);
            } else {
                conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_By_CustomerID_Branch(?,?)}");
                conn.cstm.setInt(1, user.getUserId());
                conn.cstm.setInt(2, customerID);
            }

            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                LoanContact loanContact = new LoanContact();

                loanContact.setLoanId(conn.rs.getInt("LoanID"));

                listCustomer = daCustomer.getByID(conn.rs.getInt("CustomerID"));
                Customer customer = listCustomer.get(0);
                loanContact.setCustomer(customer);

                listLoanType = daLoanType.getByID(conn.rs.getInt("LoanTypeID"));
                loanContact.setLoantype(listLoanType.get(0));


                listUser = daUser.getById(conn.rs.getInt("UserID"));
                loanContact.setUser(listUser.get(0));


                if (conn.rs.getInt("UserAccecpted") == 0) {
                    loanContact.setUserAccepted(null);
                } else {
                    listUser = daUser.getById(conn.rs.getInt("UserAccecpted"));
                    loanContact.setUserAccepted(listUser.get(0));
                }

                loanContact.setAmount(conn.rs.getDouble("Amount"));
                loanContact.setDateStart(conn.rs.getDate("DateStart"));
                loanContact.setPeriod(conn.rs.getInt("Period"));
                loanContact.setIsAccepted(conn.rs.getInt("IsAccepted"));
                list.add(loanContact);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public ArrayList<LoanContact> getByAmount(double fromAmount, double toAmount, User user) throws Exception {
        ArrayList<LoanContact> list = new ArrayList<LoanContact>();
        try {
            conn.getConnection();
            if (user.getUserType().getTypeId() == 1) {
                conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_By_Amount(?,?)}");
                conn.cstm.setDouble(1, fromAmount);
                conn.cstm.setDouble(2, toAmount);
            } else {
                conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_By_Amount_Branch(?,?,?)}");
                conn.cstm.setInt(1, user.getUserId());
                conn.cstm.setDouble(2, fromAmount);
                conn.cstm.setDouble(3, toAmount);
            }

            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                LoanContact loanContact = new LoanContact();

                loanContact.setLoanId(conn.rs.getInt("LoanID"));

                listCustomer = daCustomer.getByID(conn.rs.getInt("CustomerID"));
                Customer customer = listCustomer.get(0);
                loanContact.setCustomer(customer);

                listLoanType = daLoanType.getByID(conn.rs.getInt("LoanTypeID"));
                loanContact.setLoantype(listLoanType.get(0));


                listUser = daUser.getById(conn.rs.getInt("UserID"));
                loanContact.setUser(listUser.get(0));


                if (conn.rs.getInt("UserAccecpted") == 0) {
                    loanContact.setUserAccepted(null);
                } else {
                    listUser = daUser.getById(conn.rs.getInt("UserAccecpted"));
                    loanContact.setUserAccepted(listUser.get(0));
                }

                loanContact.setAmount(conn.rs.getDouble("Amount"));
                loanContact.setDateStart(conn.rs.getDate("DateStart"));
                loanContact.setPeriod(conn.rs.getInt("Period"));
                loanContact.setIsAccepted(conn.rs.getInt("IsAccepted"));
                list.add(loanContact);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public ArrayList<LoanContact> getByDate(Date fromDate, Date toDate, User user) throws Exception {
        ArrayList<LoanContact> list = new ArrayList<LoanContact>();
        try {
            conn.getConnection();
            if (user.getUserType().getTypeId() == 1) {
                conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_By_Date(?,?)}");
                conn.cstm.setDate(1, fromDate);
                conn.cstm.setDate(2, toDate);
            } else {
                conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_By_Date_Branch(?,?,?)}");
                conn.cstm.setInt(1, user.getUserId());
                conn.cstm.setDate(2, fromDate);
                conn.cstm.setDate(3, toDate);
            }

            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                LoanContact loanContact = new LoanContact();

                loanContact.setLoanId(conn.rs.getInt("LoanID"));

                listCustomer = daCustomer.getByID(conn.rs.getInt("CustomerID"));
                Customer customer = listCustomer.get(0);
                loanContact.setCustomer(customer);

                listLoanType = daLoanType.getByID(conn.rs.getInt("LoanTypeID"));
                loanContact.setLoantype(listLoanType.get(0));


                listUser = daUser.getById(conn.rs.getInt("UserID"));
                loanContact.setUser(listUser.get(0));


                if (conn.rs.getInt("UserAccecpted") == 0) {
                    loanContact.setUserAccepted(null);
                } else {
                    listUser = daUser.getById(conn.rs.getInt("UserAccecpted"));
                    loanContact.setUserAccepted(listUser.get(0));
                }

                loanContact.setAmount(conn.rs.getDouble("Amount"));
                loanContact.setDateStart(conn.rs.getDate("DateStart"));
                loanContact.setPeriod(conn.rs.getInt("Period"));
                loanContact.setIsAccepted(conn.rs.getInt("IsAccepted"));
                list.add(loanContact);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public ArrayList<LoanContact> getLoanContactSelect(int select, User user) throws Exception {
        ArrayList<LoanContact> list = new ArrayList<LoanContact>();
        try {
            conn.getConnection();
            if (user.getUserType().getTypeId() == 1) {
                conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_All(?)}");
                conn.cstm.setInt(1, select);
            } else {
                conn.cstm = conn.connect.prepareCall("{CALL View_LoanContact_Branch(?,?)}");
                conn.cstm.setInt(1, user.getUserId());
                conn.cstm.setInt(2, select);
            }
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                LoanContact loanContact = new LoanContact();

                loanContact.setLoanId(conn.rs.getInt("LoanID"));

                listCustomer = daCustomer.getByID(conn.rs.getInt("CustomerID"));
                Customer customer = listCustomer.get(0);
                loanContact.setCustomer(customer);

                listLoanType = daLoanType.getByID(conn.rs.getInt("LoanTypeID"));
                loanContact.setLoantype(listLoanType.get(0));


                listUser = daUser.getById(conn.rs.getInt("UserID"));
                loanContact.setUser(listUser.get(0));

                if (conn.rs.getInt("UserAccecpted") == 0) {
                    loanContact.setUserAccepted(null);
                } else {
                    listUser = daUser.getById(conn.rs.getInt("UserAccecpted"));
                    loanContact.setUserAccepted(listUser.get(0));
                }

                loanContact.setAmount(conn.rs.getDouble("Amount"));
                loanContact.setDateStart(conn.rs.getDate("DateStart"));
                loanContact.setPeriod(conn.rs.getInt("Period"));
                loanContact.setIsAccepted(conn.rs.getInt("IsAccepted"));
                list.add(loanContact);
            }
            conn.myClose();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    //insert to Loan type
    public int insert(int customerID, int loantypeID, int userID, double amount, int period, Date dateStart) throws Exception {
        int result = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{ CALL Insert_LoanContact(?,?,?,?,?,?) }");
            conn.cstm.setInt(1, customerID);
            conn.cstm.setInt(2, loantypeID);
            conn.cstm.setInt(3, userID);
            conn.cstm.setDouble(4, amount);
            conn.cstm.setInt(5, period);
            conn.cstm.setDate(6, dateStart);
            result = conn.cstm.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        conn.myClose();
        return result;
    }

    //update to loan type
    public int update(LoanContact loanContact) throws Exception {
        int result = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{ CALL Update_LoanContact(?,?,?,?,?,?,?,?,?) }");
            conn.cstm.setInt(1, loanContact.getLoanId());
            conn.cstm.setInt(2, loanContact.getCustomer().getCustomeId());
            conn.cstm.setInt(3, loanContact.getLoantype().getLoanTypeID());
            conn.cstm.setInt(4, loanContact.getUser().getUserId());
            conn.cstm.setInt(5, loanContact.getUserAccepted().getUserId());
            conn.cstm.setDouble(6, loanContact.getAmount());
            conn.cstm.setInt(7, loanContact.getPeriod());
            conn.cstm.setDate(8, loanContact.getDateStart());
            conn.cstm.setInt(9, loanContact.isIsAccepted());
            result = conn.cstm.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        conn.myClose();
        return result;
    }

    //delete from loan type
    public int accecpted(int id, int userID) throws Exception {
        int result = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL Accepted_LoanContact(?,?)}");
            conn.cstm.setInt(1, id);
            conn.cstm.setInt(2, userID);
            result = conn.cstm.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        conn.myClose();
        return result;
    }

    public int notAccecpted(int id, int userID) throws Exception {
        int result = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CALL NotAccepted_LoanContact(?,?)}");
            conn.cstm.setInt(1, id);
            conn.cstm.setInt(2, userID);
            result = conn.cstm.executeUpdate();

        } catch (Exception ex) {
        }
        conn.myClose();
        return result;
    }

    public void calculatePayment(int loanid, int loantypeID) throws Exception {
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{ CAlL Cal_Payment(?,?)}");
            conn.cstm.setInt(1, loanid);
            conn.cstm.setInt(2, loantypeID);
            conn.cstm.executeUpdate();
        } catch (Exception ex) {
        }
        conn.myClose();
    }

    public double getTotalAmount() {
        double totalAmount = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{CAlL Cal_TotalAmount}");
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                totalAmount = conn.rs.getDouble("TotalAmount");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
            conn.myClose();
        } catch (SQLException ex) {
            Logger.getLogger(DALoanContact.class.getName()).log(Level.SEVERE, null, ex);
        }

        return totalAmount;

    }

    public double getTotalLoan() {
        double totalAmount = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{ CAlL Cal_TotalLoan}");
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                totalAmount = conn.rs.getDouble("TotalLoan");
            }
        } catch (Exception ex) {
        }
        try {
            conn.myClose();
        } catch (SQLException ex) {
            Logger.getLogger(DALoanContact.class.getName()).log(Level.SEVERE, null, ex);
        }

        return totalAmount;

    }

    public double getTotalInterest() {
        double totalAmount = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{ CAlL Cal_Interest}");
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                totalAmount = conn.rs.getDouble("TotalInterest");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try {
            conn.myClose();
        } catch (SQLException ex) {
            Logger.getLogger(DALoanContact.class.getName()).log(Level.SEVERE, null, ex);
        }

        return totalAmount;

    }

    public double getTotalFine() {
        double totalAmount = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{ CAlL Cal_TotalFine}");
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                totalAmount = conn.rs.getDouble("TotalFine");
            }
        } catch (Exception ex) {
        }
        try {
            conn.myClose();
        } catch (SQLException ex) {
            Logger.getLogger(DALoanContact.class.getName()).log(Level.SEVERE, null, ex);
        }

        return totalAmount;

    }

    public double getTotalPayment() {
        double totalAmount = 0;
        try {
            conn.getConnection();
            conn.cstm = conn.connect.prepareCall("{ CAlL Cal_TotalPayment}");
            conn.rs = conn.cstm.executeQuery();
            while (conn.rs.next()) {
                totalAmount = conn.rs.getDouble("TotalPayment");
            }
        } catch (Exception ex) {
        }
        try {
            conn.myClose();
        } catch (SQLException ex) {
            Logger.getLogger(DALoanContact.class.getName()).log(Level.SEVERE, null, ex);
        }

        return totalAmount;
    }
}
